package com.jhg.drgclinicalpathway.dao;

import com.jhg.drgclinicalpathway.domain.V临床路径项目;
import org.apache.ibatis.annotations.Mapper;
import org.apache.ibatis.annotations.Param;
import org.apache.ibatis.annotations.Select;

import java.util.Base64;
import java.util.List;
import java.util.Map;

@Mapper
public interface CommonDao {

    @Select({
            "<script>",
            "exec p_获取病人大类信息 @id=#{id},@lid=#{lid}",
            "</script>"
    })
    public List<Map<String, Object>> pcbyid(@Param("id") String id, @Param("lid") Integer lid);


    @Select({
            "<script>",
            "select 说明 proSubType ,类型A proType,d.应用 as rqd from JC_疾病对应项目 a ",
            "left join V临床路径项目 b on a.项目编码=b.编码 COLLATE Chinese_PRC_BIN ",
            "left join DBO.JC_疾病对应项目_应用 d on a.id=d.id",
            "where 疾病编码=#{code} ",
            "and DATEPART(year,a.修改时间)>1900 and ISNULL(b.状态,0)=0 and  b.属性!=1 ",
//			"and 类型A in",
//			"<foreach collection='ls' open='(' close=')' item='id' index='i' separator=','>",
//			"#{id}",
//			"</foreach>",
            "AND EXISTS(SELECT 1 FROM dbo.HIS对码目录 d JOIN dbo.LJ_临床路径项目明细 c ON",
            "b.ID = c.项目ID WHERE c.项目编码 = d.医保编码 COLLATE Chinese_PRC_BIN)    ",
            "group by 说明,类型A,d.应用",
            "</script>"
    })
    public List<Map<String, Object>> getProType(@Param("code") String code, @Param("ls") List<String> ls);


    @Select({
            "<script>",
            "exec 获取疾病大类 @code=#{code},@all=#{all}",
            "</script>"
    })
    public List<Map<String, Object>> getProType2(@Param("code") String code,@Param("all") Boolean all);
    @Select({
            "<script>",
            "select  distinct top 50 b.* ",
            "<if test=\"discode!=null and discode!=''\">",
            "from(select * from JC_疾病对应项目 where DATEPART(year,修改时间)>1900",
            "and 疾病编码=#{discode}",
            ")a left join V临床路径项目 b ",
            "on a.项目编码=b.ID COLLATE Chinese_PRC_BIN",
            "</if>",
            "<if test=\"!(discode!=null and discode!='')\">",
            "from V临床路径项目 b",
            "</if>",
//            ")a left join V临床路径项目 b ",
//            "on a.项目编码=b.ID COLLATE Chinese_PRC_BIN",
//            "where EXISTS(SELECT 1 FROM dbo.HIS对码目录 d JOIN dbo.LJ_临床路径项目明细 c ON",
//            "b.ID = c.项目ID  WHERE c.项目编码 = d.医保编码 COLLATE Chinese_PRC_BIN) ",
            "where 1=1",
            "<if test=\"proType!=null and proType!=''\">",
            "and b.类型A=#{proType}",
            "</if>",
            "<if test=\"proSubType!=null and proSubType!=''\">",
            " and b.说明=#{proSubType}",
            "</if>",
            "<if test=\"s!=null and s!=''\">",
            "and (b.名称 like '%${s}%' or b.拼音简码 like '${s}%')",
            "</if>",

            "</script>"
    })
    public List<Map<String, Object>> autoName(@Param("proType") String proType,
                                              @Param("proSubType") String proSubType,
                                              @Param("discode") String discode,
                                              @Param("s") String s);

    @Select({
            "<script>",
            "SELECT top 50 a.ID 项目id,a.名称,b.项目编码,c.项目名称 ,c.规格,c.生产厂家,d.医保编码,d.售价,",
            "case a.属性 when 1 then '基础项目' when '2' then '不判断' else '' end as 项目属性",
            "FROM dbo.V临床路径项目 a JOIN dbo.LJ_临床路径项目明细 b ON a.ID = b.项目ID JOIN dbo.V收费项目 c ON b.项目编码 = c.流水号",
            "left join HIS对码目录 d on b.项目编码 = d.医保编码 AND d.险类 = '985'",
            "where a.类型=#{proType} and d.医保编码 IS NOT NULL",
            "<if test=\"s!=null and s!=''\">",
            "and (a.名称 like '%${s}%' or a.拼音简码 like '${s}%') ",
            "</if>",

            "</script>"
    })
    public List<Map<String,Object>> autoName2(@Param("proType") String proType,@Param("s") String s);

    @Select({
            "<script>",
            "select top 20  distinct b.* from(select * from JC_疾病对应项目 where DATEPART(year,修改时间)>1900",
            "<if test=\"discode!=null and discode!=''\">",
            "and 疾病编码=#{discode}",
            "</if>",
            ")a left join V临床路径项目 b ",
            "on a.项目编码=b.ID COLLATE Chinese_PRC_BIN",
            "AND EXISTS(SELECT 1 FROM dbo.HIS对码目录 d JOIN dbo.LJ_临床路径项目明细 c ON",
            "b.ID = c.项目ID WHERE c.项目编码 = d.医保编码 COLLATE Chinese_PRC_BIN) ",
            "where 1=1",
            "<if test=\"proType!=null and proType!=''\">",
            "and b.类型A=#{proType}",
            "</if>",
            "<if test=\"proSubType!=null and proSubType!=''\">",
            " and b.说明=#{proSubType}",
            "</if>",
            "<if test=\"s!=null and s!=''\">",
            "and (b.名称 like '%${s}%' or b.拼音简码 like '${s}%')",
            "</if>",

            "</script>"
    })
    public List<Map<String, Object>> selectByDis(@Param("proType") String proType,
                                                 @Param("proSubType") String proSubType,
                                                 @Param("discode") String discode,
                                                 @Param("s") String s);

    //    @Select({"select c.id,c.bm,c.mc,b.sl,c.sj dj from TMP_诊疗目录 a\n" +
//            "LEFT JOIN TMP_诊疗对应项目 b on a.id=b.zlid \n" +
//            "LEFT JOIN TMP_项目目录 c on  b.sfid=c.id where a.id=#{zlxmid}"})
    @Select({"SELECT bl.版本名称 bbmc, bl.版本ID bm,bl.项目id id, bl.项目名称 mc,bl.类型 lb ,ISNULL(d.售价, 0) dj " +
            " FROM DBO.VDCP_模板项目使用比例 BL " +
            " LEFT JOIN dbo.LJ_临床路径项目明细 b ON bl.项目id=b.项目id " +
            " LEFT JOIN dbo.HIS对码目录 d on b.项目编码 = d.医保编码  " +
            " WHERE  BL.结果 = 32 AND 项目使用占比 between 0.1 and 0.3 AND d.险类 = '985' " +
            "AND bl.版本ID ='${zlxmid}' AND bl.类型='${zllb}' "})
    public List<Map<String,Object>> ypxmByZlid(@Param("zlxmid") String zlxmid,
                                               @Param("zllb") String zllb);
    @Select({"select  b.版本说明 bbmc,b.id bm,a.项目ID as id,a.项目名称 mc,a.项目类型 lb,a.单价 dj from V路径特征标志 a  " +
            "left join [LJ_临床路径版本] b on a.路径ID=b.路径ID " +
            "where b.ID='${zlxmid}' and a.项目类型='${zllb}' and  EXISTS (SELECT mx.项目ID from LJ_临床路径明细 mx where mx.路径ID =a.路径ID  and a.项目ID=mx.项目ID)"})
    public List<Map<String,Object>> sfxmByZlid(@Param("zlxmid") String zlxmid,
                                               @Param("zllb") String zllb);

    @Select({
            "select * from 病人费用记录 where 病人标识=#{pid}"
    })
    public List<Map<String,Object>> cost(String pid);

    @Select({
            "select * from 病人诊断记录 where 病人标识=#{pid}"
    })
    public List<Map<String,Object>> dec(String pid);

    @Select({
            "select * from 病人手术记录 where 病人标识=#{pid}"
    })
    public List<Map<String,Object>> opt(String pid);

    @Select({
            "<script>",
            "select distinct top 50 说明,拼音简码,类型A,属性 from V临床路径项目 where 类型A NOT IN('药品','中药饮片')",
            "<if test=\"s!=null and s!=''\">",
            "and (说明 like '%${s}%' or 拼音简码 like '${s}%')",
            "</if>",
            "</script>",
    })
    public List<Map<String,Object>> autoProType(String s);

    @Select({
            "<script>",
            "select * from V临床路径项目 where 名称 in",
            "<foreach collection='ls' open='(' close=')' item='id' index='i' separator=','>",
            "#{id}",
            "</foreach>",
            " or 说明 in ",
            "<foreach collection='ls' open='(' close=')' item='id' index='i' separator=','>",
            "#{id}",
            "</foreach>",
            "</script>"
    })
    public List<V临床路径项目> 通过名称获取项目编码(@Param("ls") List<String> ls);

    @Select({
            "<script>",
            "select * from V临床路径项目 where id in",
            "<foreach collection='ls' open='(' close=')' item='id' index='i' separator=','>",
            "#{id}",
            "</foreach>",
            " or 说明 in ",
            "<foreach collection='ls' open='(' close=')' item='id' index='i' separator=','>",
            "#{id}",
            "</foreach>",
            "</script>"
    })
    public List<V临床路径项目> 通过项目ID获取项目编码(@Param("ls") List<String> ls);

    @Select({
            "<script>",
            "select * from V临床路径项目 where ID in",
            "<foreach collection='ls' open='(' close=')' item='id' index='i' separator=','>",
            "#{id}",
            "</foreach>",
            "</script>"
    })
    public List<V临床路径项目> V临床路径项目(@Param("ls") List<String> ls);

    @Select({
            "<script>",
            "select 说明 'key',上级ID value from V临床路径项目 where 说明 in " ,
            "<foreach collection='ls' open='(' close=')' item='id' index='i' separator=','>",
            "#{id}",
            "</foreach>",
            " and 上级ID!=''",
            "group by 说明,上级ID",
            "</script>"
    })
    public List<Map<String,String>> 通过名称获取项目编码2(@Param("ls") List<String> ls);

    @Select({
            "<script>",
            "select top 50 * from V临床路径项目 where 类型A=#{p}",
            "<if test=\"t!=null and t!=''\">",
            "and 说明=#{t}",
            "</if>",
            "<if test=\"s!=null and s!=''\">",
            "and (名称 like '%${s}%' or 拼音简码 like '${s}%')",
            "</if>",
            "</script>"
    })
    public List<Map<String,Object>> autoProject(@Param("p") String p,@Param("t") String t,@Param("s") String s);


    @Select({
            "select * from LJ_临床路径目录"
    })
    public List<Map<String,Object>> 临床路径目录();

    @Select({
            "<script>",
            "select top 50 * from ${tb} where 1=1",
            "<if test=\"w!=null and w!=''\">",
            "and ${w}",
            "</if>",
            "</script>"
    })
    public List<Map<String,Object>> sql(String tb,String w);


    @Select({
            "<script>",
            "SELECT 名称,适用范围 FROM LJ_临床路径项目 where 名称 in",
            "<foreach collection='ls' open='(' close=')' item='id' index='i' separator=','>",
            "#{id}",
            "</foreach>",
            "</script>"
    })
    public List<Map<String,Object>> 药品说明(@Param("ls") List<String> ls);


    @Select({
            "SELECT CONVERT(BIT,参数值) FROM dbo.jc_参数设置 WHERE 名称 = '是否中医院性质'"
    })
    public Boolean 中医院性质();

    @Select({
            "<script>",
            "SELECT CONVERT(varchar(7),a.出院日期,120) rq, avg(a.标准总费用-总费用) je,avg(a.总费用) 次均费用,a.标准总费用 " ,
            "from VDCP_病人信息_路径指标参考 a where a.出院日期 between #{kssj} and #{jssj}  " ,
            "<if test=\"keys!=null and keys!=''\">",
            " and (病组编码 like '%${keys}%' or 病组名称 like '${keys}%')",
            "</if>",
            " and a.[是否评估]=1 and 标杆来源=1 GROUP BY CONVERT(varchar(7),a.出院日期,120),a.标准总费用  order by rq",
            "</script>"})
    public List<Map<String, Object>> getBzqsReport(@Param("kssj") String kssj,@Param("jssj") String jssj,@Param("keys") String keys);

    @Select({"<script>" +
            " select aa.*,convert(NUMERIC(18,4),aa.总费用/bb.总费用) 总收入占比 from (\n" +
            "SELECT top ${size} DRG编码 病组编码,DRG名称 病组名称,\n" +
            "convert(NUMERIC(18,2),SUM(DRG权重)) 总权重,COUNT(1) 总入组人次,convert(NUMERIC(18,2),sum(住院总费用)) 总费用,\n" +
            "convert(NUMERIC(18,2),SUM(DDRG标准支付金额)) DRG标准支付总额,convert(NUMERIC(18,2),SUM(项目结算基金支付)) AS 项目结算基金支付总额,convert(NUMERIC(18,2),SUM(医保基金DRG支付)) 医保基金DRG支付总额,\n" +
            "convert(NUMERIC(18,2),sum(医保基金DRG支付)-SUM(项目结算基金支付)) as 盈亏\n" +
            "  FROM VDCP_入组结算明细  \n" +
            " WHERE (结算时间 BETWEEN #{kssj} and #{jssj}) and Drg编码 not like 'QY_组' and Drg编码 not in ('0000','0000组','未入组') \n" +
            " GROUP BY DRG编码,DRG名称  ",
            "<if test=\"desc =='1'.toString()\">" ,
            "HAVING convert(NUMERIC(18,2),sum(医保基金DRG支付)-SUM(项目结算基金支付)) > 0 ",
            " order by 盈亏 desc " ,
            "</if>",

            "<if test=\"desc =='2'.toString()\">" ,
            "HAVING convert(NUMERIC(18,2),sum(医保基金DRG支付)-SUM(项目结算基金支付)) &lt; 0 ",
            " order by 盈亏 " ,
            "</if>",

            ") as aa,\n" +
            "(SELECT COUNT(1) 总人次,convert(NUMERIC(18,2),sum(住院总费用)) 总费用\n" +
            "  FROM VDCP_入组结算明细  \n" +
            " WHERE (结算时间 BETWEEN #{kssj} and #{jssj}) and Drg编码 not like 'QY_组' and Drg编码 not in ('0000','0000组','未入组')) bb" +
            "</script>"})
    public List<Map<String, Object>> getBzqsHzReport(@Param("kssj") String kssj,@Param("jssj") String jssj,
                                                     @Param("size") int size, @Param("desc") String desc);
    @Select({"select 科室, count(1) 总人数,count(case [是否入径] when 1 then 1 else null end) 入径,count(重复住院) 重复住院,\n" +
            "COUNT(违规项目较多) 违规项目较多,COUNT(违反核心治疗)  违反核心治疗,COUNT(费用结构不合理) 费用结构不合理,\n" +
            "COUNT(并发症消耗过大) 并发症消耗过大,count(费用超) 费用超,count(费用超标) 费用超标  from (\n" +
            "SELECT 科室,是否入径," +
            "(case when (总费用-a.标准总费用)/a.标准总费用>=0.2 then 1 else null end) 费用超,\n" +
            "(case when [三十天内重复住院] >0 then 1 else null end) 重复住院,\n" +
            "(case when a.是否入径=1 and (select count(1) from LJ_病人评估预警 YJ11 where  a.[病人ID] = YJ11.病人标识 AND YJ11.预警ID = 11) >0 then 1 else null end) 违规项目较多," +
            "(case when a.是否入径=1 and (select count(1) from LJ_病人评估预警 YJ11 where  a.[病人ID] = YJ11.病人标识 AND YJ11.预警ID = 1) >0 then 1 else null end) 费用超标," +
            "(case when a.是否入径=1 and (select count(1) from LJ_病人评估预警 YJ13 where  a.[病人ID] = YJ13.病人标识 AND YJ13.预警ID = 13) >0 then 1 else null end) 违反核心治疗," +
            "(case when a.是否入径=1 and (select count(1) from LJ_病人评估预警 YJ12 where  a.[病人ID] = YJ12.病人标识 AND YJ12.预警ID = 12) >0 then 1 else null end) 并发症消耗过大," +
            "(case when a.是否入径=1 and (select count(1) from LJ_病人评估预警 YJ5 where  a.[病人ID] = YJ5.病人标识 AND YJ5.预警ID BETWEEN 5 AND 10) >0 then 1 else null end) 费用结构不合理 " +
            "from VDCP_病人信息_路径指标参考 a " +
            "where 在院=1  " +
            ") s GROUP BY 科室 " +
            "ORDER BY 科室"})
    public List<Map<String, Object>> getZybrRjqkReport(@Param("ksmc") String ksmc);

    @Select({"WITH CTE AS ( " +
            "SELECT BR.JZH 病人标识, PP.版本ID ,PG.项目ID  " +
            "FROM dbo.TMP_病人信息 (NOLOCK) BR  " +
            "JOIN dbo.LJ_病人路径匹配 (NOLOCK) PP ON BR.JZH = PP.病人标识 " +
            "JOIN dbo.LJ_病人路径评估 (NOLOCK) PG ON BR.JZH = PG.病人标识 AND PP.版本ID = PG.版本id " +
            "WHERE  BR.CYRQ BETWEEN CONVERT(DATETIME,#{kssj}) AND CONVERT(DATETIME, #{jssj})) " +
            "SELECT * FROM ( " +
            "SELECT BL.版本ID,BB.版本说明 版本名称,MX.项目ID,XM.名称 项目名称," +
            " cc.[生产厂家],cc.规格,"+
            "case XM.属性 when 1 then '基础项目' when '2' then '不判断' else '' end as 项目属性," +
            "CASE aa.执行性质 WHEN 1 THEN '必选' ELSE '' END 选择属性,CB.[单价],XM.类型 ," +
            " (case CB.缺省 when 1 then '是' else '否' end) 缺省,aa.序号," +
            "CONVERT(DECIMAL(18,4),ISNULL(MX.项目数, 0) / BL.病例数) 项目使用占比 " +
            " FROM  LJ_临床路径明细 aa  " +
            " LEFT JOIN LJ_临床路径明细从表 cb on aa.id=cb.明细id  " +
            " LEFT JOIN dbo.LJ_临床路径版本  BB ON aa.版本ID = BB.ID " +
            " LEFT JOIN dbo.LJ_临床路径项目  XM ON aa.项目ID = XM.ID  AND XM.场合 & 2 = 2 " +
            " LEFT JOIN dbo.V收费项目 cc  ON aa.项目id = cc.流水号 "+
            " LEFT JOIN (SELECT CTE.版本ID,COUNT(DISTINCT CTE.病人标识) * 1.00 病例数 FROM CTE GROUP BY CTE.版本ID) BL on BL.版本ID=aa.版本ID " +
            " LEFT JOIN (SELECT CTE.版本ID,CTE.项目ID,COUNT(1) 项目数  FROM CTE GROUP BY CTE.版本ID,CTE.项目ID ) MX ON MX.版本ID = aa.版本ID and MX.项目ID=aa.项目ID" +
            ") MX WHERE  MX.版本ID = #{mbid}  and 序号 is not null and 项目名称 is not null order by 序号"})
            //"AND MX.项目使用占比 >= 0.3 order by 序号"})
    public List<Map<String, Object>> getMbsyReport(@Param("kssj") String kssj,@Param("jssj") String jssj,@Param("mbid") String mbid);

    @Select({"<script>",
            "SELECT fzbm,fzmc from YB_CHS_GROUP where fzbm like '%${parm}%' or fzmc like '%${parm}%' order by fzbm",
            "</script>"})
    public List<Map<String, Object>> surchDrgGroupByBm(@Param("parm") String parm);

    @Select({"SELECT a.病人姓名,a.性别,a.年龄,a.入院日期,a.床日,a.病组编码,a.病组名称 路径名称,a.刷新时间 as 入径时间, " +
                    "case when (病组编码 <> '未入组' AND 病组编码 <> 'QYG组'  " +
                    "AND SUBSTRING(病组编码, 2, 1) IN ('R', 'S', 'T', 'U', 'V', 'W', 'X', 'Y') AND 手术费 > 500) then '诊断疑似入组错误' else '' end as 入组错误1,\n" +
                    "case when  a.总费用*1.0/b.标准总费用 < 0.7 then '总费用低于标杆费用的70%' when a.总费用*1.0/b.标准总费用>1.3 then '总费用高于标杆费用的130%' else '' end 费用2,\n" +
                    "case when  a.床日*1.0/b.标准床日 < 0.7 then '总床日低于标准床日的70%' when  a.床日*1.0/b.标准床日 < 0.7 then '总床日高于标准床日的130%' else '' end 床日3,\n" +
                    "(select case when count(*)>0 then '费用项目超出临床路径范围' else '' end ct from LJ_病人路径评估  where 病人标识=a.病人id and 结果 in (31,32)) as 是否超范围4\n" +
                    "FROM DBO.VDCP_病人信息 a " +
                    "left join [JC_路径指标参考] b on a.标杆来源ID=b.id " +
                    "WHERE 病人id =#{brid}  "})
    public List<Map<String, Object>> getPsthPatInfo(@Param("brid") String brid);

    @Select({"SELECT  出院科室名称 as 科室名称,\n" +
            "convert(NUMERIC(18,2),SUM(DRG权重)) 权重,COUNT(1) 总人次," +
            "convert(NUMERIC(18,2),sum(住院总费用)) 总费用,\n" +
            "convert(NUMERIC(18,2),SUM(DDRG标准支付金额)) DRG标准支付总额," +
            "convert(NUMERIC(18,2),SUM(项目结算基金支付)) AS 项目结算基金支付总额," +
            "convert(NUMERIC(18,2),SUM(医保基金DRG支付)) 医保基金DRG支付总额," +
            "convert(NUMERIC(18,2),SUM(DRG权重)*1.0/COUNT(1)) CMI, convert(NUMERIC(18,2),SUM(住院总费用)*1.0/COUNT(1)) 次均费用,\n" +
            "convert(NUMERIC(18,2), sum(盈亏状况)) as 盈亏\n" +
            "  FROM VDCP_入组结算明细  \n" +
            " WHERE (结算时间 BETWEEN #{kssj} AND #{jssj} ) GROUP BY 出院科室名称\n" +
            " UNION ALL \n" +
            "SELECT  '----合计----' as 科室名称,convert(NUMERIC(18,2),SUM(DRG权重)) 权重,COUNT(1) 总人次,convert(NUMERIC(18,2),sum(住院总费用)) 总费用,\n" +
            "convert(NUMERIC(18,2),SUM(DDRG标准支付金额)) DRG标准支付总额,convert(NUMERIC(18,2),SUM(项目结算基金支付)) AS 项目结算基金支付总额,convert(NUMERIC(18,2),SUM(医保基金DRG支付)) 医保基金DRG支付总额,\n" +
            "convert(NUMERIC(18,2),SUM(DRG权重)*1.0/COUNT(1)) CMI, convert(NUMERIC(18,2),SUM(住院总费用)*1.0/COUNT(1)) 次均费用," +
            "convert(NUMERIC(18,2), sum(盈亏状况)) as 盈亏\n" +
            "  FROM VDCP_入组结算明细  \n" +
            " WHERE (结算时间 BETWEEN #{kssj}  AND #{jssj} ) "})
    public List<Map<String, Object>> getRpt_Rzmx(@Param("kssj") String kssj,@Param("jssj") String jssj);

    @Select({"<script>" +
            /*"select count(1) 总例数,\n" +
            "count(rz.drg编码) 入组例数,\n" +
            "case when count(1)=0  then 0 else convert(decimal(18,2),round(count(rz.drg编码)*100.0/count(1),2)) end as 入组率,\n" +
            "convert(decimal(18,2),ISNULL(sum(case when a.病组编码 like '%QY%' OR  a.病组编码 in ('0000','0000组','未入组') then 1183.16 else (case when rz.住院总费用/rz.Ddrg标准支付金额&lt;=0.3 then rz.住院总费用 else rz.Ddrg标准支付金额-rz.住院总费用  end) end),0)) as 住院总盈亏,\n" +
            "CONVERT(decimal(18,2),ISNULL(avg(case WHEN a.病组编码 like '%QY%' OR  a.病组编码 in ('0000','0000组','未入组') then 1183.16 else (case when rz.住院总费用/rz.Ddrg标准支付金额&lt;=0.3 then rz.住院总费用 else rz.Ddrg标准支付金额-rz.住院总费用  end) end),0)) as 平均盈亏,\n" +
            "convert(decimal(18,2),ISNULL(avg(CAST(rz.床日 as float)),0)) as 平均床日 ,\n" +
            "count(distinct rz.drg编码) DRG组数,convert(decimal(18,2),ISNULL(avg(a.总费用),0)) 例均费用,\n" +
            "convert(decimal(18,2),ISNULL(avg(a.药品费/a.总费用*100),0)) 平均药占比,\n" +
            "convert(decimal(18,2),ISNULL(avg(a.耗材费/a.总费用*100),0)) 平均耗材占比,\n" +
            "convert(decimal(18,2),ISNULL(sum(rz.DRG权重),0)) as DRG权重,\n" +
            "convert(decimal(18,2),ISNULL(sum(rz.DRG权重)/count(distinct rz.出院科室名称),0)) as 平均权重,\n" +
            "count(distinct SUBSTRING(rz.drg编码,1,1)) MDC组数,\n" +
            "convert(decimal(18,2),ISNULL(sum(rz.住院总费用*1.0)/sum(rz.Ddrg标准支付金额),0)) as 费用消耗指数,\n" +
            "convert(decimal(18,2),ISNULL(sum(rz.床日*1.0)/sum(rz.标准床日),0)) as 时间消耗指数,\n" +
            "CONVERT(decimal(18,2),ISNULL(sum(rz.DRG权重)*1.0/count(rz.drg编码),0)) CMI\n" +
            "from VDCP_病人信息_路径指标参考 a\n" +
            "left join VDCP_入组结算明细 rz on rz.就诊号=a.病人ID and rz.drg编码 not like '%QY%' and rz.drg编码 not in ('0000','0000组','未入组')\n" +
            "where a.病组编码!='未入组' and a.总费用>0 and a.出院日期 between #{kssj}  AND #{jssj} "+*/
           /* " select count(1) 总例数, count(d.fzbm) 入组例数, \n" +
            "   case when count(1)=0 then 0 else convert(decimal(18,2),round(count(d.fzbm)*100.0/count(1),2)) end as 入组率,\n" +
            " convert(decimal(18,2),ISNULL(sum(case when d.fzbm is null then 1183.16 else \n" +
            "   (case when a.总费用/d.fee&lt;=0.3 then  a.总费用 else d.fee- a.总费用 end) end),0)) as 住院总盈亏, \n" +
            " CONVERT(decimal(18,2),ISNULL(avg(case WHEN d.fzbm is null then 1183.16 else \n" +
            "   (case when a.总费用/d.fee&lt;=0.3 then  a.总费用 else d.fee- a.总费用 end) end),0)) as 平均盈亏, \n" +
            " convert(decimal(18,2),ISNULL(avg(CAST(d.ch as float)),0)) as 平均床日 , count(distinct d.fzbm) DRG组数,\n" +
            " convert(decimal(18,2),ISNULL(avg(a.总费用),0)) 例均费用, convert(decimal(18,2),ISNULL(avg(a.药品费/a.总费用*100),0)) 平均药占比, \n" +
            " convert(decimal(18,2),ISNULL(avg(a.耗材费/a.总费用*100),0)) 平均耗材占比, convert(decimal(18,2),ISNULL(sum(d.qz),0)) as DRG权重, \n" +
            " convert(decimal(18,2),ISNULL(sum(d.qz)/count(distinct a.科室),0)) as 平均权重, count(distinct SUBSTRING(d.fzbm,1,1)) MDC组数, \n" +
            " convert(decimal(18,2),ISNULL(sum(a.总费用*1.0)/sum(d.fee),0)) as 费用消耗指数,  \n" +
            " convert(decimal(18,2),ISNULL(sum(a.床日*1.0)/sum(d.ch),0)) as 时间消耗指数,\n" +
            " CONVERT(decimal(18,2),ISNULL(sum(d.qz)*1.0/count(d.fzbm),0)) CMI \n" +
            " from VDCP_病人信息_路径指标参考 a  \n" +
            " left join YB_CHS_GROUP d on d.fzbm=a.病组编码 \n" +
            " where a.总费用>0  and a.出院日期 between  #{kssj}  AND #{jssj} "+*/
            "select count(1) 总例数,\n" +
            "sum(a.入组状态) 入组例数, \n" +
            "case when count(1)=0 then 0 else convert(decimal(18,2), sum(a.入组状态)*100.0/count(1)) end as 入组率,\n" +
            "convert(decimal(18,2),sum(a.盈亏状况)) as 住院总盈亏,\n" +
            "CONVERT(decimal(18,2),avg(a.盈亏状况)) as 平均盈亏, \n" +
            "convert(decimal(18,2),ISNULL(avg(a.床日),0)) as 平均床日 , \n" +
            "count(distinct a.DRG编码) DRG组数, \n" +
            "convert(decimal(18,2), ISNULL(avg(a.住院总费用),0)) 例均费用, \n" +
            "convert(decimal(18,2),ISNULL(avg(a.药品费/a.住院总费用*100),0)) 平均药占比,\n" +
            "convert(decimal(18,2),ISNULL(avg(a.耗材费/a.住院总费用*100),0)) 平均耗材占比, \n" +
            "convert(decimal(18,2),ISNULL(sum(a.Drg权重),0)) as DRG权重, \n" +
            "convert(decimal(18,2),ISNULL(sum(a.Drg权重*1.0)/count(distinct a.出院科室名称),0)) as 平均权重, \n" +
            "count(distinct SUBSTRING(a.DRG编码,1,1)) MDC组数, \n" +
            "convert(decimal(18,2),ISNULL(sum(a.住院总费用*1.0)/sum(a.Ddrg标准支付金额),0)) as 费用消耗指数, \n" +
            "convert(decimal(18,2),ISNULL(sum(a.床日*1.0)/sum(a.标准床日),0)) as 时间消耗指数, \n" +
            "CONVERT(decimal(18,2),ISNULL(sum(a.Drg权重)*1.0/count(a.DRG编码),0)) CMI \n" +
            "from VDCP_入组结算明细 a\n" +
            "where a.结算时间  between  #{kssj}  AND #{jssj}",
            "<if test=\"dept!=null and dept.size() >0\">",
            "and a.出院科室名称 in <foreach collection='dept' open='(' close=')' item='dp' index='i' separator=','>",
            "#{dp}",
            "</foreach>",
            "</if>",
            "</script>"})
    public List<Map<String, Object>> getRpt_RZQKHZ(@Param("kssj") String kssj,@Param("jssj") String jssj,@Param("dept") List<String> dept);

    @Select({"<script>" +
            /*"select " +
            "convert(decimal(18,2),sum(case when b.fzbm is null then 1183.16 else (case when a.总费用/a.标准总费用&lt;=0.3 then a.总费用 else a.标准总费用-a.总费用  end) end)) as 住院总盈亏," +
            "convert(decimal(18,2),avg(a.床日)) as 平均床日,\n" +
            "convert(decimal(18,2),sum(b.qz)) as DRG权重,\n" +
            "convert(decimal(18,2),sum(a.总费用*1.0)/sum(a.标准总费用)) as 费用消耗指数,\n" +
            "convert(decimal(18,2),sum(a.床日*1.0)/sum(b.ch)) as 时间消耗指数,\n" +
            "convert(decimal(18,2),sum(b.qz)*1.0/count(a.病组编码)) CMI,科室\n" +
            "from VDCP_病人信息_路径指标参考 a\n" +
            "left join YB_CHS_GROUP b on a.病组编码=b.fzbm\n" +
            "where a.总费用>0 and a.出院日期  between #{kssj} AND #{jssj} \n" +*/
            "select  convert(decimal(18,2),sum(a.盈亏状况))  住院总盈亏,\n" +
            "convert(decimal(18,2),avg(a.床日)) as 平均床日, \n" +
            "convert(decimal(18,2),sum(a.Drg权重)) as DRG权重, \n" +
            "convert(decimal(18,2),sum(a.住院总费用*1.0)/sum(a.Ddrg标准支付金额)) as 费用消耗指数, \n" +
            "convert(decimal(18,2),sum(a.床日*1.0)/sum(a.标准床日)) as 时间消耗指数, \n" +
            "convert(decimal(18,2),sum(a.Drg权重)*1.0/count(a.DRG编码)) CMI,\n" +
            "出院科室名称 科室 \n" +
            "from VDCP_入组结算明细 a\n" +
            "where a.结算时间 between #{kssj} AND #{jssj}  ",
            "<if test=\"dept!=null and dept.size() >0\">",
            "and a.出院科室名称 in <foreach collection='dept' open='(' close=')' item='dp' index='i' separator=','> \n",
            "#{dp}",
            "</foreach>",
            "</if>",
            "group by 出院科室名称 </script>"})
    public List<Map<String, Object>> getRpt_RZQKFk(@Param("kssj") String kssj,@Param("jssj") String jssj,@Param("dept") List<String> dept);

    @Select({"<script>" +
            /*"select a.病组编码 病组, " +
            "convert(decimal(18,2),avg(case when b.fzbm is null then 1183.16 else (case when a.总费用/b.fee&lt;=0.3 then a.总费用 else b.fee-a.总费用  end) end)) as 盈亏,count(1) 人次," +
            "convert(decimal(18,2),sum(b.qz)*1.0/count(a.病组编码)) CMI " +
            "from VDCP_病人信息_路径指标参考 a " +
            "left join YB_CHS_GROUP b on a.病组编码=b.fzbm " +
            "join (select top ${size} ck.病组编码 as 编码,count(1) ct from VDCP_病人信息_路径指标参考 ck " +
            "where  ck.出院日期 between #{kssj} AND #{jssj}  " +
            "<if test=\"dept!=null and dept!=''\">",
            "and ck.科室 = '${dept}'",
            "</if>",
            "and ck.病组编码 not like '%组' and ck.病组编码!='0000' group by ck.病组编码 order by ct desc) c on a.病组编码=c.编码 " +
            "where a.总费用>0 and b.fzbm is not null and a.出院日期 between #{kssj} AND #{jssj} " +*/
            "select top ${size} a.DRG编码 病组, \n" +
            "AVG(a.盈亏状况) 盈亏, count(1) 人次, \n" +
            "convert(decimal(18,2),sum(a.Drg权重)*1.0/count(a.DRG编码)) CMI \n" +
            "from VDCP_入组结算明细 a\n" +
            "where a.入组状态=1 and a.结算时间 between #{kssj} AND #{jssj} ",
            "<if test=\"dept!=null and dept!=''\">",
            "and a.出院科室名称 = '${dept}'",
            "</if>",
            "group by  a.DRG编码 order by 人次 desc </script>"})
    public List<Map<String, Object>> getRpt_RZQKFBZ(@Param("kssj") String kssj,@Param("jssj") String jssj,@Param("size") int size,@Param("dept") String dept);

    @Select({"select *,convert(decimal(18,2),中医药费用*100.0/总费用) 中医药费用占比 from (\n" +
            "select a.住院号,a.病人姓名 姓名,参保类别 医保类别,a.科室 出院科室,a.出院日期 出院时间,a.床日,a.总费用,\n" +
            "(select sum(JE) from TMP_费用信息 where JZH=a.病人ID and (srxm='中草药费' or XMMC in ('神经阻滞治疗','手指点穴','电针','肩周炎推拿治疗',\n" +
            "'穴位注射','隔物灸法(隔姜灸)','中频脉冲电治疗','贴敷疗法(小)','隔物灸法','小针刀治疗','电子生物反馈疗法(脑电)','煎药机煎药',\n" +
            "'牵引','牵引(颈椎电动牵引加收)','颈椎病推拿治疗','其他推拿治疗','腰椎间盘突出推拿治疗','关节错缝术','关节穿刺术','小针刀治疗(腰椎间盘突出症加收)'))) 中医药费用\n" +
            "from VDCP_病人信息  a where 出院日期 between #{kssj} AND #{jssj}) as f\n" +
            "where 中医药费用*100.0/总费用>${zzyb}"})
    public List<Map<String, Object>> getRpt_Zyzbtj(@Param("kssj") String kssj,@Param("jssj") String jssj,@Param("zzyb") int zzyb);

    @Select({
            "SELECT A.病组编码,A.病组名称,AVG(a.[总费用]) 次均费用,COUNT(*) 人次,SUM(ISNULL(a.总费用,0.00)) 实际总费用,SUM(ISNULL(B.标准总费用,0.00)) 标准总费用,SUM(ISNULL(A.手术室费用,0.00)) 手术室费用,",
            "SUM(ISNULL(A.手术室费用,0.00))/SUM(a.[总费用])*100 手术室费用占比",
            "FROM DBO.VDCP_病人信息 A",
            "LEFT JOIN DBO.JC_路径指标参考 B ON A.标杆来源ID = B.ID",
            "WHERE A.病组类型 = 1 AND A.[病组编码] LIKE '_[BCDEFGJ]%'",
            "AND A.出院日期 BETWEEN #{stime} AND #{etime}",
            "GROUP BY A.病组编码,A.病组名称"
    })
    public List<Map<String,Object>> 外科手术室开单统计(@Param("stime") String stime,@Param("etime") String etime);

    @Select({
            "<script>",
            "EXEC p_重点控费项目分析 @开始时间=#{stime},@结束时间=#{etime},@版本号=#{vs}",
            "</script>"
    })
    public List<Map<String,Object>> P_重点控费项目分析(@Param("stime") String stime,@Param("etime") String etime,@Param("vs") String vs);

    @Select({
            "<script>",
            "EXEC P_经常漏查的项目分析 @开始时间=#{stime},@结束时间=#{etime},@版本号=#{vs}",
            "</script>"
    })
    public List<Map<String,Object>> P_经常漏查的项目分析(@Param("stime") String stime,@Param("etime") String etime,@Param("vs") String vs);
}